/*
Navicat MySQL Data Transfer

Source Server         : my
Source Server Version : 50610
Source Host           : localhost:3306
Source Database       : xingcheng

Target Server Type    : MYSQL
Target Server Version : 50610
File Encoding         : 65001

Date: 2017-09-04 12:20:17
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `examresult`
-- ----------------------------
DROP TABLE IF EXISTS `examresult`;
CREATE TABLE `examresult` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `exam_date` date DEFAULT NULL,
  `score` float(6,2) NOT NULL,
  `st_id` int(10) unsigned DEFAULT NULL COMMENT '考试学生',
  `sb_id` int(10) unsigned DEFAULT NULL COMMENT '考试的科目',
  PRIMARY KEY (`id`),
  KEY `fk_stu` (`st_id`),
  KEY `fk_subject` (`sb_id`),
  CONSTRAINT `fk_stu` FOREIGN KEY (`st_id`) REFERENCES `student` (`stuno`),
  CONSTRAINT `fk_subject` FOREIGN KEY (`sb_id`) REFERENCES `subject` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of examresult
-- ----------------------------
INSERT INTO examresult VALUES ('1', '2017-09-01', '90.00', '1', '3');

-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gname` varchar(100) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gname` (`gname`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO grade VALUES ('5', 'Java');
INSERT INTO grade VALUES ('1', 'S1');
INSERT INTO grade VALUES ('2', 'S2');
INSERT INTO grade VALUES ('3', 'Y2');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sname` varchar(40) NOT NULL COMMENT '姓名',
  `sex` enum('男','女','妖','人妖') DEFAULT '男' COMMENT '性别',
  `telephone` varchar(15) DEFAULT NULL COMMENT '电话',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `wechat` varchar(50) DEFAULT NULL COMMENT '微信',
  `gradeid` int(20) unsigned NOT NULL COMMENT '年级',
  `enterdate` date DEFAULT NULL COMMENT '入学日期',
  `fromcity` varchar(100) DEFAULT NULL COMMENT '从那个城市来的',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `address` varchar(400) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`stuno`),
  KEY `fk_grade2` (`gradeid`),
  CONSTRAINT `fk_grade2` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES ('1', '王武', '男', '131800433221', '14574934', null, '1', null, '阜阳', null, '阜阳');
INSERT INTO student VALUES ('2', '王莽', '男', '131800433221', null, null, '2', '2017-03-10', '合肥', null, null);
INSERT INTO student VALUES ('3', '李星', '女', '131800433221', '1949944', null, '3', '2017-04-10', '合肥', null, '阜阳');
INSERT INTO student VALUES ('5', '李八', '女', '131800433221', '666666', null, '3', '2017-04-20', '六安', null, '阜阳颖州区');
INSERT INTO student VALUES ('6', '牛三', '妖', '13590812397', '14574934', '14574934', '2', '2017-04-21', '六安', null, null);
INSERT INTO student VALUES ('7', '丁玉锁', '男', null, null, null, '5', null, '阜阳', '1995-05-01', null);
INSERT INTO student VALUES ('8', '丁钢锁', '男', null, null, null, '5', null, '阜阳', '1997-05-01', null);

-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subjectname` varchar(100) NOT NULL,
  `classhour` int(10) unsigned DEFAULT NULL,
  `gradeid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_grade` (`gradeid`),
  CONSTRAINT `fk_grade` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO subject VALUES ('1', 'java 基础', '16', '5');
INSERT INTO subject VALUES ('3', 'java oop', '20', '2');
INSERT INTO subject VALUES ('4', 'java高级特性', '8', '5');
INSERT INTO subject VALUES ('6', '利用html+css设计网页', '19', '3');
INSERT INTO subject VALUES ('14', '使用jquery实现网页特效', '12', '5');
INSERT INTO subject VALUES ('15', '数据库编程', '12', '5');
INSERT INTO subject VALUES ('16', 'java web编程', '16', '5');
INSERT INTO subject VALUES ('17', '深入mybatis', '10', '5');
INSERT INTO subject VALUES ('18', 'spring详解', '8', '5');
INSERT INTO subject VALUES ('19', 'struts2初步', '3', '5');
INSERT INTO subject VALUES ('20', 'hibernate浅窥', '2', '5');



-- 5:给每个表插入3条数据(insert)
-- -----------------------------
-- --给考试examresult成绩增加3条记录
-- -- ----------------------------

INSERT INTO examresult ( `exam_date`,`score`,`st_id`,`sb_id`)
VALUES('2017-09-01',95,2,1),('2017-08-25',96,3,4),('2017-07-6',99,5,6);

-- -----------------------------
-- --给年级grade增加3条记录
-- -- ----------------------------
INSERT INTO grade (`gname`)
VALUES('html'),('css'),('mysql');

-- -----------------------------
-- --给学生 student增加3条记录
-- -- ----------------------------

INSERT INTO student(`sname`,`sex`,`telephone`,`qq`,`wechat`,`gradeid`,`enterdate`,`fromcity`,`birthday`,`address`)
VALUES( '李晓丽','女','18963236325','5899999','9632665',1,'2017-05-29','芜湖','1992-05-16','芜湖'),
( '程露','女','18963586325','558899','129665',5,'2017-04-10','滁州','1992-05-16','滁州'),
( '马涛','男','13363586325','96612399','4529665',8,'2017-04-24','安庆','1994-10-28','滁州');


-- -----------------------------
-- --给科目表 SUBJECT增加3条记录
-- -- ----------------------------
INSERT INTO `subject` (`subjectname`,`classhour`,`gradeid`)
VALUES ('mysql',220,7),('HTML',150,6),('JAVA 线程',60,8);

-- 6:找出不是六安的男学生信息

SELECT * FROM student WHERE  NOT(fromcity='六安' AND sex='男');

-- 每月最后一天出生的学生信息

SELECT * FROM student WHERE birthday IN(SELECT LAST_DAY(birthday) FROM student);

UPDATE student SET birthday=DATE_ADD('1978-5-8',INTERVAL stuno MONTH) WHERE birthday IS NULL;

SELECT * FROM student;
 
SELECT * FROM examresult;
SELECT count(exam_date),AVG(score)  FROM examresult  where sb_id='3';

DROP TABLE IF EXISTS tongxun;
CREATE TABLE tongxun AS
SELECT sname,telephone from student;

SELECT id ,score FROM examresult  where exam_date='2016-2-17' ORDER BY score DESC LIMIT 5;




SELECT sname,birthday,birthday,telephone FROM student where sex='女' ORDER BY birthday ASC LIMIT 1, 6;


-- SELECT * from student WHERE stuno


SELECT id, MAX(score),MIN(score),AVG(score) FROM examresult WHERE exam_date='2016-2-17'


SELECT * FROM student WHERE birthday=(
SELECT MAX(birthday) FROM  student

)

SELECT * FROM student WHERE 

SELECT subjectNo FROM WHERE subject subjectName='java 基础';

SELECT st_id FROM examresult where id=(SELECT id FROM subject WHERE subjectName='java 基础')



SELECT * FROM student WHERE stuno IN (
SELECT st_id FROM examresult where  sb_id IN (SELECT id FROM subject WHERE subjectName='java 基础')
) 



SELECT subjectname from subject where gradeid in (

SELECT id from grade where gname='S1')

SELECT id FROM subject WHERE subjectName='HTML'
SELECT st_id FROM examresult where sb_id=(SELECT id FROM subject WHERE subjectName='HMML')



SELECT * FROM student WHERE stuno NOT IN(

SELECT st_id FROM examresult where sb_id = (SELECT id FROM subject WHERE subjectName='HMML')

)





-- -----------------------------------------------
-- 9 月6日作业
-- 3：找出所有80后的学生，即1980-1989之间出生的学生

-- ------------------------------------------------
UPDATE student SET birthday='1985-6-18' where sname='王武';



SELECT * from student WHERE YEAR(birthday) >=1980 AND YEAR(birthday)<=1989;


-- -----------------------------------------------


-- 找出来自 合肥 地区，有考试低于60的学生信息

------------------------------------------------


SELECT * FROM student WHERE stuno IN (

SELECT st_id FROM examresult WHERE score<60
) AND fromcity='合肥';

-- 如果有S2的学生，就查询参加S2学科考试的学员学号、科目编号、考试成绩，考试时间


-- 1:找到S2的年级编号
SELECT id  FROM grade WHERE gname='s2';
-- 2:通过编号 找到对应年级的学生
SELECT stuno FROM student WHERE  gradeid IN ( SELECT id  FROM grade WHERE gname='s2')

--  3.根据年级，找到对应年级的必修课

SELECT id FROM `subject` WHERE gradeid IN (SELECT id  FROM grade WHERE gname='s2')
-- 4：查询S2学员的考试结果
SELECT * FROM examresult WHERE EXISTS (

SELECT stuno FROM student WHERE  gradeid IN ( SELECT id  FROM grade WHERE gname='s2')
) AND sb_id  IN (SELECT id FROM `subject` WHERE gradeid IN (SELECT id  FROM grade WHERE gname='s2'));



-- 伪表 聚合函数不能做伪表字段名 所以要加一个别名 如下面的平均分，后面where 条件要用别名，t 是伪表名
SELECT * FROM (
SELECT sb_id,AVG(score) as 平均分 FROM examresult
GROUP BY sb_id)t
WHERE t.平均分>=85


-- 先分组 再统计
SELECT sex, MAX(birthday),MIN(birthday),COUNT(*) FROM student
GROUP BY sex;

SELECT fromcity,sex,COUNT(*) FROM student
GROUP BY fromcity,sex;

-- 去除重复 第一种方式
SELECT DISTINCT(fromcity) FROM student 
GROUP BY fromcity;
-- 去除重复 第二种方式
SELECT fromcity FROM student
GROUP BY fromcity;

SELECT fromcity,COUNT(*) FROM student
GROUP BY fromcity
HAVING COUNT(*)>2;

-- 并集 UNION  把俩张表的数据放在一起

SELECT stuno,sname FROM student
UNION
SELECT id,subjectname FROM `subject`;

-- 列出学生的信息和考试成绩， 没有考过的同学也要列出来

SELECT stuno, sname,score FROM student s LEFT JOIN examresult e

ON (s.stuno=e.st_id);
-- 
-- 左外链接
SELECT  g.id, s.subjectname FROM grade g   LEFT JOIN subject s
ON (g.id=s.gradeid)

-- 右外链接

SELECT  g.id, s.subjectname FROM grade g  RIGHT JOIN subject s
ON (g.id=s.gradeid)

-- 下面是9月7日作业
-- 1:查询累计购买考试次数 超过3次的学生信息（全部）。

-- 考试超过3次 也就是学号在考试表里面出现3次以上
--   先统计学号出现的次数
SELECT st_id , COUNT(*) FROM examresult 
GROUP BY st_id
HAVING COUNT(*)>3


-- 通过符合条件的学号找出学生信息

SELECT * FROM student WHERE stuno IN (
SELECT st_id FROM examresult 
GROUP BY st_id
HAVING COUNT(*)>3 )

如果一个学生
SELECT * FROM st



-- 2.查询有过补考的学生信息（同一门课超过2次，认为是补考）

-- （1）.先找出每个学生 都考了哪些科目， 并且统计科目数
SELECT st_id ,sb_id, COUNT(*) FROM examresult
GROUP BY sb_id,st_id
HAVING COUNT(*)>2




--   （2）然后找出符合条件的学号 输出学生信息

SELECT * FROM student  WHERE stuno IN (

SELECT st_id FROM examresult
GROUP BY sb_id,st_id
HAVING COUNT(*)>2

)


-- 3.最近半年内入学的学生


select * from student where enterdate between date_sub(now(),interval 6 month) and now();


-- 4: 查询年级信息，以及改年级的课程数，没有课程的也要显示，对应的课程数目为0




SELECT g.id as 年级编号,gname as 年级名称,COUNT(s.subjectname) AS 课程数 FROM grade g LEFT JOIN subject s 
ON (g.id=s.gradeid)
GROUP BY g.id;

-- 胡永举 的
SELECT id AS 年级,COUNT(gradeid) AS 课程数 FROM(
	SELECT G.id,S.gradeid FROM grade AS G LEFT OUTER JOIN subject AS S ON G.id=S.gradeid
)t GROUP BY id



-- 内连接的另外一种 写法

SELECT * FROM grade g,`subject` s

WHERE g.id=s.gradeid;


SELECT s.sname AS 姓名, g.gname AS 年级名称, b.subjectname 课程名称, e.exam_date AS 考试日期,e.score 考试成绩

FROM student s  left JOIN examresult e  ON( e.st_id=s.stuno)
LEFT JOIN  `subject` sb on (e.sb_id=b.id)


-- 查询年级信息，以及该年级的课程数， 没有课程的也要显示，对应课程的课程数为0


SELECT g.*,(SELECT COUNT(*) FROM `subject` s WHERE s.gradeid=g.id) as 课程数
from grade g;




SELECT  * FROM student  WHERE telephone REGEXP '^1[345]'


-- ----找出各个年级最小的学生


SELECT * from  student WHERE  gradeid IN (SELECT gradeid,MAX(birthday) FROM student GROUP BY gradeid);

SELECT stuno FROM (SELECT stuno,gradeid,MAX(birthday) FROM student GROUP BY gradeid) AS temp;


-- 这种写法 其他数据可能不可以

SELECT * from student where stuno IN (SELECT stuno FROM (SELECT stuno,gradeid,MAX(birthday) FROM student GROUP BY gradeid) AS temp)

SELECT * from student where birthday IN (SELECT  MAX(birthday) FROM student GROUP BY gradeid)


-- 找出各个年级最小的学生全部信息
SELECT s.* FROM student s ,

( SELECT gradeid,MAX(birthday) as minbir from student GROUP BY gradeid
) t

WHERE s.gradeid=t.gradeid AND s.birthday= t.minbir;



-- 找出各个年级最小的学生全部信息
SELECT * from  student WHERE (gradeid,birthday) IN ( SELECT gradeid,MAX(birthday) FROM student GROUP BY gradeid)




SELECT * FROM student WHERE  sname LIKE '丁%锁%';

BEGIN;

INSERT INTO examresult  (exam_date,score,st_id,sb_id)
VALUES ('2017-9-11',98,7,1),('2017-9-11',95,8,1),('2017-9-11',90,5,1)

SELECT * FROM examresult

COMMIT;

BEGIN;



INSERT INTO examresult  (exam_date,score,st_id,sb_id)
VALUES ('2017-9-11',105,2,1)


ROLLBACK

-- 创建视图 包含 姓名 qq 年龄

CREATE VIEW stuinfo 

AS

SELECT sname,qq,( YEAR(NOW())-YEAR(birthday)) AS 年龄 FROM student;

SELECT * FROM stuinfo; 



CREATE VIEW stu

AS

SELECT sname,qq,( YEAR(NOW())-YEAR(birthday)) AS 年龄 FROM student where ( YEAR(NOW())-YEAR(birthday)) >30;

SELECT * FROM stu; 


USE 刘老师课堂演示表;

-- 查看学生各科目考试成绩平均分2-1

CREATE VIEW stuScore 

AS  SELECT (SELECT s.sname FROM student s WHERE s.stuno= e.st_id) AS 学生姓名,( SELECT b.subjectname FROM subject b WHERE b.id= e.sb_id) AS 课程名,  AVG(score) AS 平均成绩

FROM  examresult e GROUP BY sb_id;

SELECT * FROM stuScore; 









DROP view stu

CREATE VIEW view_student
AS 
SELECT stuno,sname  FROM student

SELECT * from  view_student;


DROP VIEW IF EXISTS view_student_result;
CREATE VIEW view_student_result

AS
SELECT s.sname AS 姓名,s.stuno AS 学号,e.score as 成绩,b.subjectname AS 课程名,e.exam_date AS 考生日期
FROM student s
INNER JOIN examresult e  on (s.stuno=e.st_id)
INNER JOIN subject b ON (e.sb_id=b.id)
WHERE b.id= ( SELECT id FROM `subject` WHERE subjectname='java 基础')
AND exam_date=( SELECT MAX(exam_date) FROM examresult t,subject j
WHERE t.sb_id=j.id AND subjectname='java 基础');


SELECT * FROM view_student_result


USE information_schema;
SELECT * FROM VIEWS;

USE 刘老师课堂演示表;


CREATE INDEX index_student_sname
ON student (sname);

-- 9月11日作业

-- 3:设计高分成绩单视图，只包含成绩在80分以上的学生信息，包括科目名称以及成绩


DROP VIEW IF EXISTS view_stu_scores;
CREATE VIEW view_stu_scores
AS SELECT s.sname AS 姓名,s.stuno AS 学号,b.subjectname as 科目,e.score,e.exam_date AS 考试时间 FROM examresult e
INNER JOIN student s ON ( s.stuno=e.st_id)
INNER JOIN subject b ON ( b.id=e.sb_id)
WHERE e.score>=80;




SELECT * FROM  view_stu_scores



